package com.saas.ability.core.base.util;

import com.google.common.collect.Lists;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述:
 */
public class ExcelUtils {

    /**
     * Excel 2003
     */
    private final static String XLS = "xls";
    /**
     * Excel 2007
     */
    private final static String XLSX = "xlsx";

    /**
     * 分隔符
     */
    private final static String SEPARATOR = "|";

    public  void excelExport() throws Exception{
        String xlsFile = "f:/clroleprice.xlsx";
        long  startTime = System.currentTimeMillis();   //开始时间
        System.out.println("strat execute time: " + startTime);

        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号
        int temp = 10000000;

        for(int i = 0;i<temp;i++){
            //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
            if(rowNo%300000==0){
                System.out.println("Current Sheet:" + rowNo/300000);
                sheet = wb.createSheet("我的第"+(rowNo/300000)+"个工作簿");//建立新的sheet对象
                sheet = wb.getSheetAt(rowNo/300000);        //动态指定当前的工作表
                pageRowNo = 0;      //每当新建了工作表就将当前工作表的行号重置为0
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 打印每行，每行有6列数据   rsmd.getColumnCount()==6 --- 列属性的个数
            for(int j=0;j<6;j++){
                nCell = nRow.createCell(j);
                nCell.setCellValue("1");
            }

           /* if(rowNo%10000==0){
                System.out.println("row no: " + rowNo);
            }*/
            //      Thread.sleep(1);    //休息一下，防止对CPU占用，其实影响不大
        }

        long finishedTime = System.currentTimeMillis(); //处理完成时间
        System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");

        FileOutputStream fOut = new FileOutputStream(xlsFile);
        wb.write(fOut);
        fOut.flush();       //刷新缓冲区
        fOut.close();

        long stopTime = System.currentTimeMillis();     //写文件时间
        System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");
    }

    //设置单元格样式
    private static CellStyle leftStyle(Workbook wb){
        CellStyle curStyle = wb.createCellStyle();
        Font curFont = wb.createFont();								//设置字体
        //curFont.setFontName("Times New Roman");						//设置英文字体
        curFont.setFontName("微软雅黑");								//设置英文字体
        curFont.setCharSet(Font.DEFAULT_CHARSET);					//设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short)10);						//字体大小
        curStyle.setFont(curFont);

        curStyle.setBorderTop(CellStyle.BORDER_THICK);				//粗实线
        curStyle.setBorderBottom(CellStyle.BORDER_THIN);			//实线
        curStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);			//比较粗实线
        curStyle.setBorderRight(CellStyle.BORDER_THIN);				//实线

        curStyle.setWrapText(true);  									//换行
        curStyle.setAlignment(CellStyle.ALIGN_RIGHT);				//横向具右对齐
        curStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);	//单元格垂直居中

        return curStyle;
    }

    public static void main(String[] args) throws Exception {
        ExcelUtils excelUtils = new ExcelUtils();
        excelUtils.jdbcex();
    }

    public void jdbcex() throws InstantiationException, IllegalAccessException,
            ClassNotFoundException, IOException, InterruptedException {

        String xlsFile = "f:/poiSXXFSBigData.xlsx";     //输出文件
        //内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook();           //关键语句
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        long  startTime = System.currentTimeMillis();   //开始时间
        System.out.println("strat execute time: " + startTime);
//
      //  int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号
        int temp = 100000;
        //while(temp > 0) {
        for(int i = 0;i<temp;i++){
            //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
            if(i%300000==0){
                System.out.println("Current Sheet:" + i/300000);
                sheet = wb.createSheet("我的第"+(i/300000)+"个工作簿");//建立新的sheet对象
                sheet = wb.getSheetAt(i/300000);        //动态指定当前的工作表
                pageRowNo = 0;      //每当新建了工作表就将当前工作表的行号重置为0
            }
          //  rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 打印每行，每行有6列数据   rsmd.getColumnCount()==6 --- 列属性的个数
            for(int j=0;j<30;j++){
                nCell = nRow.createCell(j);
                nCell.setCellValue(j+"ddfdfdfdfdfdd");
            }

           /* if(rowNo%10000==0){
                System.out.println("row no: " + rowNo);
            }*/
            //      Thread.sleep(1);    //休息一下，防止对CPU占用，其实影响不大
            temp--;
        }

        long finishedTime = System.currentTimeMillis(); //处理完成时间
        System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");

        FileOutputStream fOut = new FileOutputStream(xlsFile);
        wb.write(fOut);
        fOut.flush();       //刷新缓冲区
        fOut.close();

        long stopTime = System.currentTimeMillis();     //写文件时间
        System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");


    }

    /**
     * 由Excel文件的Sheet导出至List
     *
     * @param file
     * @param sheetNum
     * @return
     */
    public static List<String> exportListFromExcel(File file, int sheetNum)
            throws IOException {
        return exportListFromExcel(new FileInputStream(file),
                FilenameUtils.getExtension(file.getName()), sheetNum);
    }


    /**
     * 由Excel流的Sheet导出至List
     *
     * @param is
     * @param extensionName
     * @param sheetNum
     * @return
     * @throws IOException
     */
    public static List<String> exportListFromExcel(InputStream is,
                                                   String extensionName, int sheetNum) throws IOException {

        Workbook workbook = null;

        if (extensionName.toLowerCase().endsWith(XLS)) {
            workbook = new HSSFWorkbook(is);
        } else if (extensionName.toLowerCase().endsWith(XLSX)) {
            workbook = new XSSFWorkbook(is);
        }

        return exportListFromExcel(workbook, sheetNum);
    }


    /**
     * 由指定的Sheet导出至List
     *
     * @param workbook
     * @param sheetNum
     * @return
     * @throws IOException
     */
    private static List<String> exportListFromExcel(Workbook workbook,
                                                    int sheetNum) {

        Sheet sheet = workbook.getSheetAt(sheetNum);
        DecimalFormat df = new DecimalFormat("#");

        // 解析公式结果
        FormulaEvaluator evaluator = workbook.getCreationHelper()
                .createFormulaEvaluator();

        List<String> list = new ArrayList<String>();
        String tempResult = "";

        int minRowIx = sheet.getFirstRowNum();
        int maxRowIx = sheet.getLastRowNum();
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
            Row row = sheet.getRow(rowIx);
            if(null == row){
                return null;
            }
            StringBuilder sb = new StringBuilder();

            short minColIx = row.getFirstCellNum();
            short maxColIx = row.getLastCellNum();
            for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
                Cell cell = row.getCell(new Integer(colIx));
                if(null != cell){
                    tempResult = df.format(cell.getNumericCellValue());
                }
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                    continue;
                }
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
                switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        sb.append(cellValue.getBooleanValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        // 这里的日期类型会被转换为数字类型，需要判别后区分处理
                        if (DateUtil.isCellDateFormatted(cell)) {
                            sb.append(cell.getDateCellValue());
                        } else {
                            sb.append(tempResult);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        sb.append(tempResult);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    default:
                        break;
                }
                list.add(sb.toString());
            }
        }
        return list;
    }

    public static final boolean checkExtension(String extension) {
        return Lists.newArrayList("xls","xlsx", "XLS","XLSX").contains(extension);
    }

}
